package com.tmp.util;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;

import com.pvo.vo.ColumnVO;
import com.pvo.vo.TableVO;

public class JdbcUtil {

	private static Connection conn = null;
	
	static {
		try {
			Class.forName(TmpConstant.datasource_driverClassName);
			try {
				conn = DriverManager.getConnection(TmpConstant.datasource_url, TmpConstant.datasource_username, TmpConstant.datasource_password);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	private static TableVO getTable(String tableName) {
		if (tableName == null || tableName.trim().length() <= 0 || conn == null) {
			return null;
		}
	//	tableName = tableName.toUpperCase();
		DatabaseMetaData databaseMetaData = null;
		TableVO tableVO = null;
		try {
			databaseMetaData = conn.getMetaData();
			ResultSet resultSet = databaseMetaData.getTables(null, null, tableName, new String[]{"TABLE"});
			while(resultSet.next()) {
				String comment = resultSet.getString("REMARKS");
				String name = resultSet.getString("TABLE_NAME");
				tableVO = new TableVO();
				tableVO.setComment(comment);
				tableVO.setName(name);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return tableVO;
			
	}
	
	
	/**
	 * 获取所有主键
	 * @param table
	 * @return
	 */
	private static List<String> getPrimaryKeyList(String table) {
		if (table == null || table.trim().length() <= 0 || conn == null) {
			return null;
		}
		List<String> list = new ArrayList<String>();
		DatabaseMetaData databaseMetaData;
		try {
			databaseMetaData = conn.getMetaData();
			ResultSet pkResultSet = databaseMetaData.getPrimaryKeys(null, null, table);
			while (pkResultSet.next()) {
				list.add(pkResultSet.getString("COLUMN_NAME"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 获取表的列的信息
	 * @param table
	 * @return
	 */
	private static List<ColumnVO> getTableColumnInfo(String table) {
		if (table == null || table.trim().length() <= 0 || conn == null) {
			return null;
		}

		List<ColumnVO> list = new ArrayList<ColumnVO>();
		try {
			DatabaseMetaData databaseMetaData = conn.getMetaData();
			ResultSet colRet = databaseMetaData.getColumns(null, "%", table, "%");
			while (colRet.next()) {
				// 列名
				String columnName = colRet.getString("COLUMN_NAME");
				// 列类型
				String columnType = colRet.getString("TYPE_NAME");
				// 列长度
				int size = colRet.getInt("COLUMN_SIZE");
				// 列的小数位数
				int digits = colRet.getInt("DECIMAL_DIGITS");
				// 是否可为空(1是， 0否)
				int nullable = colRet.getInt("NULLABLE");
				// 列注释
				String columnRemark = colRet.getString("REMARKS");
				list.add(new ColumnVO(columnName, columnType, size, digits, nullable == 1 ? true : false, columnRemark));

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	public static TableVO loadTable(String table) {
		if(StringUtils.isEmpty(table)) {
			return null;
		}
		// 获取表信息
		TableVO tableVO = getTable(table);
		if (tableVO == null) {
			return null;
		}
		// 获取列信息
		List<ColumnVO> columnList = getTableColumnInfo(table);
		if (columnList != null && columnList.size() > 0) {
			List<String> pkList = getPrimaryKeyList(table);
			for (ColumnVO columnVO : columnList) {
				boolean isPk = false;
				if (pkList != null && pkList.size() > 0) {
					for (String pkColumnName : pkList) {
						if (pkColumnName.equalsIgnoreCase(columnVO.getName())) {
							isPk = true;
							break;
						}
					}
				}
				columnVO.setPrimaryKey(isPk);
			}
		}
		tableVO.setColumnList(columnList);
		
		// 将所有信息转换为大写
		tableVO.setName(tableVO.getName().toUpperCase());
		if (columnList != null && columnList.size() > 0) {
			for (ColumnVO columnVO : columnList) {
				columnVO.setName(columnVO.getName().toUpperCase());
			}
		}
		return tableVO;
	}

	public static void main(String[] args) {
//		List<ColumnVO> columnVOs = getTableColumnInfo("TD_CATE_PUB");
//		for (Iterator iterator = columnVOs.iterator(); iterator.hasNext();) {
//			ColumnVO columnVO = (ColumnVO) iterator.next();
//			System.out.println(columnVO);
//		}
//		System.out.println(getPrimaryKey("TD_CATE_PUB"));
//		System.out.println(getTable("TD_CATE_PUB"));
		System.out.println(loadTable("product_00"));
	}
}
